<?php
require_once(dirname(__FILE__).'/../../../oci8upgrade.php');
class create_catassoc_history_table extends OCI8UPGRADE 
{
	/**
	* IsAlreadyUpgraded
	* Do we need to run this classes upgrade script or not ?
	*
	* @return bool true if the upgrade has already run, otherwise false
	*/
	function IsAlreadyUpgraded()
	{
		return $this->TableExists($GLOBALS['tablePrefix'].'CATASSOC_HISTORY');
	}

	/**
	* Upgrade
	* Do the actual upgrade of this component. If this is successful all
	* subsequent calls to IsAlreadyUpgraded should return true
	*
	* @return void
	*/
	function Upgrade()
	{
		$query = "
		CREATE TABLE ".$GLOBALS['tablePrefix']."CATASSOC_HISTORY (
		  categoryid NUMBER(11) DEFAULT '0' NOT NULL,
		  questionid NUMBER(11) DEFAULT '0' NOT NULL,
		  CONSTRAINT ".$GLOBALS['tablePrefix']."CATASSOC_HISTORY_PK PRIMARY KEY (categoryid, questionid)
		)
		";			
		$result = $GLOBALS['AKB_DB']->Query($query);

		$query = "
		CREATE INDEX ".$GLOBALS['tablePrefix']."CAH_I_QCIDS ON ".$GLOBALS['tablePrefix']."CATASSOC_HISTORY (questionid, categoryid) 
		";			
		$result = $GLOBALS['AKB_DB']->Query($query);
		
		$query = "SELECT ca.categoryid, ca.questionid "
		. "FROM ".$GLOBALS['tablePrefix']."categoryassociations ca , "
		. $GLOBALS['tablePrefix']."questions q "
		. "WHERE q.questionid = ca.questionid "
		. "AND q.workflowstatus != 'pending' ";
		$result = $GLOBALS['AKB_DB']->Query($query);
		while ($row = $GLOBALS['AKB_DB']->Fetch($result)) {
				$insertQuery = "INSERT INTO ".$GLOBALS['tablePrefix']."catassoc_history (categoryid,questionid) values ('".$row['categoryid']."','".$row['questionid']."')";
				$insertResult = $GLOBALS['AKB_DB']->Query($insertQuery);
		}
		
	}
}
?>
